package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.bo.*;
import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import java.sql.*;
import java.util.List;
import java.util.LinkedList;

  /**
 * Salesperson Data Access Object
 * @author Adam Chavez
 */
public class SalespersonDAO
{

  /////////////////////////////////////////////
  ///   Singleton code

  private static SalespersonDAO instance = null;

  /** Creates a new instance of SalespersonDAO */
  private SalespersonDAO()
  {

  }//constructor

  /** Retrieves the single instance of this class */
  public static synchronized SalespersonDAO getInstance()
  {
    if (instance == null)
    {
      instance = new SalespersonDAO();
    }
    return instance;
  }//getInstance

  ////////////////////////////////////////////
  ///   CREATE methods

  /**
   * Creates a new Salesperson in the database
   * set alreadyInDB to false
   * put Salesperson in cache
   */
  public Salesperson create(String id) throws DataException
  {
    Salesperson salesperson = new Salesperson(id);
    salesperson.setObjectAlreadyInDB(false);
    Cache.getInstance().put(salesperson.getId(), salesperson);
    return salesperson;
  }//create


  ////////////////////////////////////////////
  ///   READ methods

  /**
   * Reads an existing Salesperson from the database
   * 1) Checks cache
   * 2) Gets a connection
   * 3) Calls read with connection argument
   */
  public Salesperson read(String id) throws DataException
  {
    if (Cache.getInstance().containsKey(id))
    {
      return (Salesperson)Cache.getInstance().get(id);
    }
    Connection conn = ConnectionPool.getInstance().get();
    try
    {
      return read(id, conn);
    }
    catch (Exception e)
    {
      throw new DataException("An error occurred while reading the business object information.", e);
    }
    finally
    {
      ConnectionPool.getInstance().release(conn);
    }
  }

  /**
   * Internal method to read an existing Salesperson from the database
   * 1) Check cache for object
   * 2) Create a prepared statement to execute sql query
   * 3) Create a resultset from the preparedstatement
   * 4) Return the Salesperson
   */
  synchronized Salesperson read(String id, Connection conn) throws Exception
  {
    if (Cache.getInstance().containsKey(id))
    {
      return (Salesperson)Cache.getInstance().get(id);
    }
    PreparedStatement stmt = conn.prepareStatement("select * FROM salesperson, person, employee WHERE salesperson.id = person.id AND employee.id = person.id and person.id = ?");
    try
    {
      stmt.setString(1, id);
      ResultSet rs = stmt.executeQuery();
      if (rs.next())
      {
        return readRecord(rs, conn);
      }//if
      throw new DataException("Salesperson with id '" + id + "' not found.");
    }
    finally
    {
      stmt.close();
    }
  }//read


  /**
   * Internal method to create a Salesperson object from a record
   * 1) Check cache for object
   * 2) Instantiate new Salesperson
   * 3) Set alreadyInDb to false
   * 4) Set all variables from result set
   * 5) Set dirty to true
   * 6) Return Salesperson
   */
  synchronized Salesperson readRecord(ResultSet rs, Connection conn) throws Exception
  {
    if (Cache.getInstance().containsKey(rs.getString("id")))
    {
      return (Salesperson)Cache.getInstance().get(rs.getString("id"));
    }
    Salesperson salesperson = new Salesperson(rs.getString("id"));
    salesperson.setObjectAlreadyInDB(true);
    System.out.println("Result set" + rs.getString("FIRST_NAME"));
    salesperson.setFirstName(rs.getString("FIRST_NAME"));
    salesperson.setLastName(rs.getString("LAST_NAME"));
    salesperson.setAddress(rs.getString("ADDRESS"));
    salesperson.setPhone(rs.getString("PHONE"));
    salesperson.setPassword(rs.getString("PASSWORD"));
    salesperson.setUsername(rs.getString("USERNAME"));
    salesperson.setPosition(rs.getString("EMP_POSITION"));
    salesperson.setCommissionPercent(Float.parseFloat(rs.getString("COMMISSION_PERCENT")));

    Cache.getInstance().put(salesperson.getId(), salesperson);
    salesperson.setDirty(false);
    return salesperson;
  }//readRecord

  /////////////////////////////////////////////
  ///   UPDATE methods

  /** Saves an existing salesperson in the database */
  public void save(Salesperson salesperson) throws DataException
  {
   Connection conn = ConnectionPool.getInstance().get();
    try
    {
      save(salesperson, conn);
      conn.commit();
    }
    catch (Exception e)
    {
      try
      {
        conn.rollback();
      }
      catch (SQLException e2)
      {
        throw new DataException("Could not roll back the database transaction!", e2);
      }
      throw new DataException("An error occurred while saving the business object information.", e);
    }
    finally
    {
      ConnectionPool.getInstance().release(conn);
    }

  }

  /** Internal method to update a salesperson in the database */
  void save(Salesperson salesperson, Connection conn) throws Exception
  {
    Cache.getInstance().put(salesperson.getId(), salesperson);
    if (salesperson.isObjectAlreadyInDB() && salesperson.isDirty())
    {
      update(salesperson, conn);
    }
    else if (salesperson.isDirty())
    {
      insert(salesperson, conn);
    }

    /*for (SaleLine si: sale.getLines())
    {
      SaleLineDAO.getInstance().save(si, conn);
    }*/
  }

  /** Saves an existing salesperson to the database */
  private void update(Salesperson salesperson, Connection conn) throws Exception
  {
    PreparedStatement stmt = conn.prepareStatement("UPDATE person SET first_name=?, last_name=?, address=?, phone=? WHERE id=?");
    try
    {
      stmt.setString(1, salesperson.getFirstName());
      stmt.setString(2, salesperson.getLastName());
      stmt.setString(3, salesperson.getAddress());
      stmt.setString(4, salesperson.getPhone());
      stmt.setString(5, salesperson.getId());
      stmt.execute();
      salesperson.setDirty(false);
    }
    finally
    {
      stmt.close();
    }

    stmt = conn.prepareStatement("UPDATE employee SET username=?, password=?, emp_position=? WHERE id=?");
    try
    {
      stmt.setString(1, salesperson.getUsername());
      stmt.setString(2, salesperson.getPassword());
      stmt.setString(3, salesperson.getPosition());
      stmt.setString(4, salesperson.getId());
      stmt.execute();
      salesperson.setDirty(false);
    }
    finally
    {
      stmt.close();
    }

    stmt = conn.prepareStatement("UPDATE salesperson SET commission_percent=? WHERE id=?");
    try
    {
      stmt.setString(1, salesperson.getCommissionPercent() + "");
      stmt.setString(2, salesperson.getId());
      stmt.execute();
      salesperson.setDirty(false);
    }
    finally
    {
      stmt.close();
    }
  }

  /** Inserts a new salesperson into the database */
  private void insert(Salesperson salesperson, Connection conn) throws Exception
  {
    PreparedStatement stmt = conn.prepareStatement("INSERT INTO person VALUES (?, ?, ?, ?, ?)");
    try
    {
      stmt.setString(1, salesperson.getId());
      stmt.setString(2, salesperson.getFirstName());
      stmt.setString(3, salesperson.getLastName());
      stmt.setString(4, salesperson.getAddress());
      stmt.setString(5, salesperson.getPhone());
      stmt.execute();
      salesperson.setObjectAlreadyInDB(true);
      salesperson.setDirty(false);
    }
    finally
    {
      stmt.close();
    }

    stmt = conn.prepareStatement("INSERT INTO employee VALUES (?, ?, ?, ?)");
    try
    {
      stmt.setString(1, salesperson.getId());
      stmt.setString(2, salesperson.getUsername());
      stmt.setString(3, salesperson.getPassword());
      stmt.setString(4, salesperson.getPosition());
      stmt.execute();
      salesperson.setDirty(false);
    }
    finally
    {
      stmt.close();
    }

    stmt = conn.prepareStatement("INSERT INTO salesperson VALUES (?, ?)");
    try
    {
      stmt.setString(1, salesperson.getId());
      stmt.setString(2, salesperson.getCommissionPercent() + "");
      stmt.execute();
      salesperson.setDirty(false);
    }
    finally
    {
      stmt.close();
    }
  }





  /////////////////////////////////////////////////
  ///   DELETE methods

  /** We do not support deleting of business objects in this application */
  public void delete(Salesperson salesperson) throws DataException
  {
    throw new RuntimeException("Nice try. The delete function is not supported in this application.");
  }



  ////////////////////////////////////////////////
  ///   SEARCH methods

  /** Retrieves all conceptualcars from the database */
  public List<Salesperson> getAll() throws DataException
  {
    Connection conn = ConnectionPool.getInstance().get();
    try
    {
      PreparedStatement stmt = conn.prepareStatement("SELECT *  FROM salesperson, person, employee WHERE salesperson.id = person.id AND employee.id = person.id ORDER BY person.id");
      return search(stmt, conn);
    }
    catch (Exception e)
    {
      try
      {
        conn.rollback();
      }
      catch (SQLException e2)
      {
        throw new DataException("Could not roll back the database transaction!", e2);
      }
      throw new DataException("An error occurred while reading the business object information.", e);
    }
    finally
    {
      ConnectionPool.getInstance().release(conn);
    }  //establish a connection, use a prepared statement, plug it into the search method return a list of empoyees
  }

/** Internal method to search by certain fields */
  private List<Salesperson> search(PreparedStatement stmt, Connection conn) throws Exception
  {
    List<Salesperson> salespersonList = new LinkedList<Salesperson>();
    try
    {
      ResultSet rs = stmt.executeQuery();
      while (rs.next())
      {
        salespersonList.add(readRecord(rs, conn));
      }
    }
    finally
    {
      stmt.close();
    }
    return salespersonList;
  }

 /* public List<Salesperson> getByName(String fName, String lName) throws Data Exception
  {
	//establish connection and prepared statement run it through the search method, return a list of salespersons matching the name(maybe more than one)
  }

  public Salesperson getBySalespersonNumber(String EmpID) throws Data Exception
  {
	//establish connection and prepared statement run it through the search method, return an Salesperson matching the EmpID
  }*/



}
